﻿using System;
using System.Data;
using System.Data.Sql;
using System.Diagnostics;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;

using Builderator.Core.Action;
using Builderator.Core.Interfaces;

namespace Builderator.Core.Database
{
    public class DatabaseAdapter
    {
        private DatabaseInfo databaseInfo;
        private SqlDataAdapter dataAdapter;
        private SqlConnection databaseConnection;

        public List<DatabaseInfo> Servers
        {
            get
            {
                return this.GetServers();
            }
        }

        public DatabaseAdapter()
        {
            try
            {
                Configuration databaseConfig;

                databaseConfig = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

                this.databaseInfo = (DatabaseInfo)databaseConfig.GetSection("builderator");

                if (this.databaseInfo == null)
                {
                    this.databaseInfo = new DatabaseInfo();

                    databaseConfig.Sections.Add("builderator", this.databaseInfo);

                    databaseConfig.Save();
                }

                this.databaseConnection = new SqlConnection();

                this.databaseConnection.ConnectionString = this.databaseInfo.ToString();

                this.databaseConnection.Open();

                this.dataAdapter = new SqlDataAdapter();
            }
            catch (Exception e)
            {
                Trace.WriteLine(e.Message + "\n" + e.StackTrace);
            }
        }

        public List<DatabaseInfo> GetServers()
        {
            List<DatabaseInfo> servers = new List<DatabaseInfo>();

            SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;

            foreach (DataRow row in instance.GetDataSources().Rows)
            {
                if (row["InstanceName"].Equals("Builderator"))
                {
                    servers.Add(new DatabaseInfo { Server = (string)row["ServerName"] });
                }
            }

            return servers;
        }

        public void Reconnect(string serverName)
        {
            this.databaseInfo.Server = serverName;

            this.databaseConnection.Close();

            Configuration databaseConfig;

            databaseConfig = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

            databaseConfig.Save();

            this.databaseConnection = new SqlConnection();

            this.databaseConnection.ConnectionString = this.databaseInfo.ToString();

            this.databaseConnection.Open();

            this.dataAdapter = new SqlDataAdapter();

        }

        public void Insert(DatabaseTable table, IDatabase action)
        {
            try
            {
                this.dataAdapter.InsertCommand = action.Insert(table);

                this.dataAdapter.InsertCommand.Connection = this.databaseConnection;

                this.dataAdapter.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Trace.WriteLine(e.Message + "\n" + e.StackTrace);
            }
        }

        public List<string[]> Select(DatabaseTable table, IDatabase action)
        {
            SqlDataReader reader = null;

            try
            {
                List<string[]> results = new List<string[]>();

                this.dataAdapter.SelectCommand = action.Select(table);

                this.dataAdapter.SelectCommand.Connection = this.databaseConnection;

                reader = this.dataAdapter.SelectCommand.ExecuteReader();

                string[] items;

                while (reader.Read())
                {
                    items = new string[reader.FieldCount];

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        items[i] = reader[i] as string;
                    }

                    results.Add(items);
                }

                reader.Close();

                return results;
            }
            catch (Exception e)
            {
                Trace.WriteLine(e.Message + "\n" + e.StackTrace);

                return null;
            }
            finally
            {
                reader.Close();
            }

        }

        public List<string[]> Select(string sqlCommand)
        {
            SqlDataReader reader = null;

            try
            {
                List<string[]> results = new List<string[]>();

                this.dataAdapter.SelectCommand = new SqlCommand(sqlCommand);

                this.dataAdapter.SelectCommand.Connection = this.databaseConnection;

                reader = this.dataAdapter.SelectCommand.ExecuteReader();

                string[] items;

                while (reader.Read())
                {
                    items = new string[reader.FieldCount];

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        items[i] = reader[i] as string;
                    }

                    results.Add(items);
                }

                reader.Close();

                return results;
            }
            catch (Exception e)
            {
                Trace.WriteLine(e.Message + "\n" + e.StackTrace);

                return null;
            }
            finally
            {
                reader.Close();
            }
        }

        public void Update(DatabaseTable table, IDatabase action)
        {
            try
            {
                this.dataAdapter.UpdateCommand = action.Update(table);

                this.dataAdapter.UpdateCommand.Connection = this.databaseConnection;

                int test = this.dataAdapter.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Trace.WriteLine(e.Message + "\n" + e.StackTrace);
            }
        }

        public void Delete(DatabaseTable table, IDatabase action)
        {
            try
            {
                this.dataAdapter.DeleteCommand = action.Delete(table);

                this.dataAdapter.DeleteCommand.Connection = this.databaseConnection;

                this.dataAdapter.DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Trace.WriteLine(e.Message + "\n" + e.StackTrace);
            }
        }

        public void RegisterPlugin(IDatabase action)
        {
            try
            {
                this.dataAdapter.InsertCommand = action.Insert(DatabaseTable.Plugins);

                this.dataAdapter.InsertCommand.Connection = this.databaseConnection;

                this.dataAdapter.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Trace.WriteLine(e.Message + "\n" + e.StackTrace);
            }
        }
    }
}